This chapter presents some of the concepts and issues of client/server development with Visual Basic 4.0. Some illustrative examples are used. This chapter also introduces some common terms used in client/server development.
At least two computers exist in the basic client/server environment: the client, which interacts directly with the user, and the server, which processes queries and returns results. This architecture is sometimes referred to as two tier (see Figure
33.1). Visual Basic is a tool that can be used to create programs on the client computer. Commercially available RDBMS servers, such as Oracle, SQL Server, and so on are used to process the queries that the Visual Basic client program issues.
Figure 33.1. An example of two-tier architecture.
In two-tier implementations, the business rules are incorporated into the client machine or the server, or they are shared by both. In a three-tier client server solution, a third computer is inserted between the client machine and the server to
implement some or all of the business logic (see Figure 33.2). This frees up the resources of the server to focus more exclusively on directly fulfilling query requests, and it reduces the burden on the client machine. The software running on the middle
computer sometimes is referred to as middleware. There are very few standards in this relatively new area. This chapter therefore focuses on the two-tier model.
Figure 33.2. An example of three-tier architecture.
In contrast to a client/server database system, an ISAM (Indexed Sequential Access Method) database forces all processing to be done by the user computer. Some common databases that fall into this category are the XBase types (dBASE and FoxPro, among
others) and the Microsoft Access database. For more detail on ISAM databases, turn back to Chapter 31, "Creating, Modifying, and Maintaining Databases."
Note
A well-designed client server system can have several advantages over an ISAM database system:
The referential integrity of a database can be better maintained with a client/server RDBMS for the following reasons:
Network traffic can be reduced when using a client/server RDBMS solution. In an ISAM application, all record access is carried out by the user program. Any search or series of updates requires the user computer to receive unrelated database records and
index information over the network so that it can locate the proper spot in which to make the change it needs. In simple terms, this means that an ISAM database has substantial irrelevant transactions over the network. A client server solution, on the
other hand, executes its database operations first by sending a query that describes exactly what it wants done, and then receiving through the network only exactly what it wanted. No irrelevant network traffic is generated under ideal conditions. Query
and connection considerations exist when using Visual Basic 4.0, which impact on the degree of idealized communication between the client computer and the RDBMS server. These considerations are discussed later in the section Creating a Nice Client.
Because queries are carried out by the RDBMS computer independently of the client computer, a degree of parallel processing is already taking place. While the query is being fulfilled, the user computer is available to focus on other computational
tasks. This is not the same as multitasking, which Windows already does. This is better. In multitasking, parallel processing is only simulated by switching between application program instructions behind the scenes so that it seems as though everything is
running at once. The key to the difference here is seems to be running at once. With one CPU doing everything, this is only a trick. With two independent CPUs, there is no trick: parallel processing is real. When using a Visual Basic 4.0 query on an ISAM
database, that query is executed in its entirety by the user computer. When a client/server RDBMS is used to execute a user query, the user machine is free to work on other tasks.
Some commercial RDBMS servers—such as Oracle, SYBASE, and MS SQL Server—are able to utilize more than one CPU on the server computer. This capability gives those RDBMS servers a scalability not available to ISAM database applications. If you
develop a client/server application using Visual Basic and your database server is scaleable with respect to CPUs, you can increase query performance and server responsiveness without recoding your program as you add users and clients. If your RDBMS
supports it, you can replace your server hardware with a multi-CPU PC and transfer the existing database to it. This process is transparent to your Visual Basic client/server application.
All the advantages of client/server systems do not come free. Unlike ISAM database applications, the client/server model introduces at least one other machine into the picture that must be configured and debugged in order for the solution to work.
Although development of a client application using ODBC for client/server connectivity can be straightforward and not much different from development of an ISAM program, each RDBMS server is at least slightly different from other vendor versions and
requires specialized knowledge about that system.
Depending on the size and needs of a project, it may be wise to have a dedicated Database Administrator (DBA) who works exclusively with the database server to configure it and maintain the databases. Most RDBMS servers today still need or greatly
benefit from periodic tuning. Tuning steps and procedures are not standard across server platforms.
There are three versions of Visual Basic 4.0: Standard, Professional, and Enterprise. The Enterprise version adds enhancements specifically geared for client/server developers. However, you can use any version to work with client/server databases
through ODBC.
In general, although the order can be different, these are the steps you should take to create an application for a three-tier client/server architecture:
Creating an application for a two-tier architecture only requires steps 1 and 3. Since there are few standards for interfacing to the middleware layer of a three-tier architecture, I will focus on the two-tier model here.
Several tools are available from third parties to assist with the design and implementation of a database on RDBMS servers. These tools make the job easier by providing graphical interfaces and generating the SQL creation commands for you. The exact
process of implementing databases on RDBMS servers varies from vendor to vendor. You should consult your vendor's documentation for the details. If you are lucky and have one, however, you can just ask your DBA to create the database for you.
After your database is designed and implemented on your server, you are ready to connect to it via ODBC. To access your server via this method, you have to ensure that the proper ODBC driver is installed on your system. ODBC drivers are available from
several sources, one of which is usually the RDBMS vendor. Some companies, such as Intersolve, sell developer packages that contain drivers for many RDBMS systems, including Oracle and SQL Server.
You can access your database directly through ODBC or indirectly if your database tables are attached to an Access-type MDB file. Attaching your tables to an Access MDB file has several advantages—including those of performance—over directly
accessing tables through code and ODBC. An attached table is managed by the Visual Basic Jet Engine so that its connection information is preserved between queries. If this information is not preserved, every query has to reestablish authorization with the
target database before the query can be executed.
If you are using ODBC, you can issue RDBMS-specific commands by employing ODBC passthrough queries. This technique, sometimes referred to as SQL PassThrough, bypasses the Microsoft Jet database engine and sends your commands directly to the RDBMS. If
your RDBMS supports stored procedures, you will need to use this technique to run them. (A stored procedure is a small program stored on the server.)
The reason cutting Jet out of the picture can be an advantage is that, in its effort to help processing along, the Jet engine sometimes ends up creating more network traffic and stealing work from the RDBMS server.
Extra network traffic is created when the Jet engine shares action query processing by requesting data in chunks from the server to process it locally. Anything modified is then sent back to the server for storage. In an idealized client/server
scenario, this doesn't happen; the server does all the processing with no back and forth transmission of the work in process. ODBC passthrough ensures reduced network overhead by moving all the processing to the server. Only the result comes back to the
user computer.
Like most things, passthrough queries have a price. A fundamental disadvantage of implementing applications that rely heavily on passthrough queries is reduced portability. If the passthrough commands are RDBMS specific, you may need to recode these
queries to use the application with a different RDBMS.
Since passthrough queries are not analyzed by the Jet engine, they have to be coded exactly as the RDBMS expects to see them. Some SQL queries, such as joins in particular, vary in structure from vendor to vendor. The common interface presented by Jet
is an advantage that leaves when passthrough is relied on heavily.
Not only is the element of a common SQL interface reduced when Jet is removed from the processing, losing Jet can also mean you lose some query functionality. Jet allows native Visual Basic functions in query constructions. RDBMS servers do not have
access to the Visual Basic parser, so Visual Basic's intrinsic functions, such as Now() and Mid$(), are not available.
Although most major RDBMSs have functions similar to the ones Visual Basic allows in Jet queries, they have no way of integrating parameterized query prompting into a Visual Basic program. A parameterized query through the Jet engine can accept the
parameter directly from the user or a program variable. An RDBMSs parameter query value must be supplied directly as a literal in the passthrough string.
Another possible disadvantage is that passthrough queries only return snapshots. Snapshots are not updatable. As a result, if there is anything in the result of a passthrough query that requires update, another query has to be issued.
Two terms heard frequently with describing client applications are fat client and thin client. A fat client is one that incorporates many business rules into the user application. A thin client relies more significantly on the server or a middleware
server for its business rule logic. Visual Basic client/server applications can be fat or thin clients. Some of the factors that determine the mode are obvious and some are subtle.
Assuming that the BTS database was implemented in a client/server RDBMS, a simple example of a fat client would be an application that returns the entire contents of the tblCandidate database and filters out all non Mr records at the user workstation to
display the Mr entries. Such a query follows:
SELECT * FROM tblCandidate;
Alternatively, a thin version of this application might issue the following query:
SELECT * FROM tblCandidate WHERE tblCandidate.Title = Mr;
The significance of this difference is that in the fat client version, the server had less logical processing to execute but more raw data to transmit. Additionally, the user machine had more processing to execute in the fat client version.
In general, it is better to develop thin clients for the following reasons:
If your RDBMS supports stored procedures and triggers, such as Oracle and SQL Server, you can use them to help keep your client thin. Stored procedures and triggers are programs that execute on the server itself. They usually are written in the language
provided by the server system for that purpose. The particulars of their implementation are unique to the server you use, so consult your DBA or system documentation.
Some processing automatically is performed locally, even by the Jet Engine under some circumstances in Visual Basic 4.0. In particular, the Jet Engine may process some joins locally, causing increased network traffic and slower performance. Consult the
Visual Basic 4.0 manuals for the specific situations in which this can happen.
There are several reasons to consider Visual Basic 4.0 for client/server development:
With few exceptions, creating client/server applications in Visual Basic requires no special knowledge beyond that required to create ISAM database applications. The most significant exceptions are those with respect to configuring the RDBMS server, but
that is actually independent of the application development itself in a logical sense. An office with a dedicated DBA and a Visual Basic programmer can begin creating client/server applications without any special training beyond that which they already
have to perform non-Visual Basic client/server development.
Visual Basic's support for ODBC makes it possible to develop generic client/server applications that can work with multiple database back end solutions. In fact, unless ODBC passthrough is used, porting a Visual Basic client/server program to another
back end is only a matter of selecting a different ODBC driver on the user workstation. If the ODBC driver information is not explicitly coded into the application or stored in an MDB attached table, no program change is necessary.
Visual Basic was one of the first Windows application development tools to have a genuinely intuitive and powerful screen designer. Given that a very significant amount of development time can be spent laying out the user interface and that users form
significant impressions from the way the application is presented, this is a very significant factor in a tool's usability. With the advent of version 4, Visual Basic has enhanced the designer by enabling the form layout to lock down after it is set to the
designer's liking. Additionally, the right button on the mouse now provides ready access to Control properties, which make configuration even faster and easier. Although some other tools—notably, Delphi from Borland—have recently made significant
inroads into screen designer usability, Visual Basic still has one of the easiest and most intuitive screen designers available.
This chapter introduced some of the concepts and techniques of client/server development with Visual Basic 4.0. It focused on the common factors of this type of development without going into much detail about specific RDBMS systems or techniques. For more information on client/server RDBMS systems, consult your particular system's documentation.
